<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>MySQL 优化指南 | 技术小馆</title>
    <link rel="stylesheet" href="https://cdn.staticfile.org/font-awesome/6.4.0/css/all.min.css">
    <link rel="stylesheet" href="https://cdn.staticfile.org/tailwindcss/2.2.19/tailwind.min.css">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@400;500;600;700&family=NotoSans+SC:wght@300;400;500;700&display=swap" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/mermaid@latest/dist/mermaid.min.js"></script>
    <style>
        body {
            font-family: 'Noto Sans SC', Tahoma, Arial, Roboto, "Droid Sans", "Helvetica Neue", "Droid Sans Fallback", "Heiti SC", "Hiragino Sans GB", Simsun, sans-serif;
            color: #333;
            line-height: 1.6;
        }
        h1, h2, h3, h4 {
            font-family: 'Noto Serif SC', serif;
            font-weight: 600;
        }
        .hero-gradient {
            background: linear-gradient(135deg, #3a7bd5 0%, #00d2ff 100%);
        }
        .card-hover:hover {
            transform: translateY(-5px);
            box-shadow: 0 10px 25px rgba(0, 0, 0, 0.1);
        }
        .highlight-box {
            border-left: 4px solid #3a7bd5;
            background-color: #f8fafc;
        }
        .dropdown-content {
            display: none;
            opacity: 0;
            transition: opacity 0.3s ease;
        }
        .dropdown:hover .dropdown-content {
            display: block;
            opacity: 1;
        }
        .mermaid {
            background-color: white;
            border-radius: 0.5rem;
            padding: 1.5rem;
            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.05);
        }
    </style>
</head>
<body class="bg-gray-50">
    <!-- Hero Section -->
    <section class="hero-gradient text-white py-20 md:py-32 px-4 md:px-0">
        <div class="container mx-auto max-w-5xl text-center">
            <h1 class="text-4xl md:text-6xl font-bold mb-6 animate-fade-in">MySQL 优化指南</h1>
            <p class="text-xl md:text-2xl mb-8 max-w-3xl mx-auto">提升数据库性能的艺术与科学</p>
            <div class="flex justify-center space-x-4">
                <a href="#optimization-methods" class="bg-white text-blue-600 hover:bg-blue-50 px-6 py-3 rounded-full font-medium transition-all duration-300 transform hover:scale-105">优化方法</a>
                <a href="#performance-tuning" class="bg-transparent border-2 border-white hover:bg-white hover:text-blue-600 px-6 py-3 rounded-full font-medium transition-all duration-300 transform hover:scale-105">性能调优</a>
            </div>
        </div>
    </section>

    <!-- Table of Contents -->
    <section class="py-12 bg-white">
        <div class="container mx-auto max-w-5xl px-4">
            <div class="bg-blue-50 rounded-xl p-6 shadow-sm">
                <h2 class="text-2xl font-semibold mb-6 text-blue-800 flex items-center">
                    <i class="fas fa-list-ol mr-3"></i> 内容导航
                </h2>
                <div class="grid grid-cols-1 md:grid-cols-3 gap-4">
                    <a href="#query-optimization" class="block bg-white p-4 rounded-lg hover:shadow-md transition-all">
                        <h3 class="font-medium text-blue-700 mb-2">查询优化</h3>
                        <p class="text-sm text-gray-600">EXPLAIN 分析、索引优化等</p>
                    </a>
                    <a href="#schema-optimization" class="block bg-white p-4 rounded-lg hover:shadow-md transition-all">
                        <h3 class="font-medium text-blue-700 mb-2">Schema 优化</h3>
                        <p class="text-sm text-gray-600">数据类型、范式与反范式</p>
                    </a>
                    <a href="#configuration-tuning" class="block bg-white p-4 rounded-lg hover:shadow-md transition-all">
                        <h3 class="font-medium text-blue-700 mb-2">配置调优</h3>
                        <p class="text-sm text-gray-600">参数配置、硬件优化</p>
                    </a>
                </div>
            </div>
        </div>
    </section>

    <!-- Main Content -->
    <main class="container mx-auto max-w-5xl py-12 px-4">
        <!-- Query Optimization -->
        <section id="query-optimization" class="mb-16">
            <div class="flex items-center mb-8">
                <div class="bg-blue-100 p-3 rounded-full mr-4">
                    <i class="fas fa-search text-blue-600 text-xl"></i>
                </div>
                <h2 class="text-3xl font-bold text-gray-800">查询优化</h2>
            </div>

            <div class="grid grid-cols-1 md:grid-cols-2 gap-8 mb-10">
                <div class="bg-white p-6 rounded-xl shadow-sm card-hover transition-all duration-300">
                    <h3 class="text-xl font-semibold mb-4 text-blue-700">EXPLAIN 分析</h3>
                    <p class="mb-4">使用 EXPLAIN 分析查询执行计划是优化的第一步，可以帮助识别性能瓶颈。</p>
                    <div class="highlight-box p-4 mb-4">
                        <code class="text-sm">EXPLAIN SELECT * FROM users WHERE username = 'admin';</code>
                    </div>
                    <p>重点关注 type、key、rows、Extra 等列，识别全表扫描等低效操作。</p>
                </div>

                <div class="bg-white p-6 rounded-xl shadow-sm card-hover transition-all duration-300">
                    <h3 class="text-xl font-semibold mb-4 text-blue-700">索引优化</h3>
                    <ul class="space-y-2">
                        <li class="flex items-start">
                            <i class="fas fa-check-circle text-green-500 mt-1 mr-2"></i>
                            <span>为 WHERE、JOIN、ORDER BY 子句创建合适索引</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle text-green-500 mt-1 mr-2"></i>
                            <span>避免过度索引，索引会增加写入开销</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle text-green-500 mt-1 mr-2"></i>
                            <span>使用复合索引时注意最左前缀原则</span>
                        </li>
                    </ul>
                </div>
            </div>

            <div class="bg-white p-6 rounded-xl shadow-sm mb-8">
                <h3 class="text-xl font-semibold mb-4 text-blue-700">查询重构</h3>
                <div class="grid grid-cols-1 md:grid-cols-2 gap-6">
                    <div>
                        <h4 class="font-medium mb-2 text-gray-700">避免 SELECT *</h4>
                        <p class="text-sm text-gray-600">只查询需要的列，减少数据传输量</p>
                    </div>
                    <div>
                        <h4 class="font-medium mb-2 text-gray-700">使用 JOIN 替代子查询</h4>
                        <p class="text-sm text-gray-600">在多数情况下 JOIN 性能优于子查询</p>
                    </div>
                    <div>
                        <h4 class="font-medium mb-2 text-gray-700">LIMIT 分页优化</h4>
                        <p class="text-sm text-gray-600">避免大偏移量，使用 WHERE 条件替代</p>
                    </div>
                    <div>
                        <h4 class="font-medium mb-2 text-gray-700">避免使用 OR 条件</h4>
                        <p class="text-sm text-gray-600">可能导致索引失效，考虑使用 UNION</p>
                    </div>
                </div>
            </div>
        </section>

        <!-- Schema Optimization -->
        <section id="schema-optimization" class="mb-16">
            <div class="flex items-center mb-8">
                <div class="bg-purple-100 p-3 rounded-full mr-4">
                    <i class="fas fa-database text-purple-600 text-xl"></i>
                </div>
                <h2 class="text-3xl font-bold text-gray-800">Schema 优化</h2>
            </div>

            <div class="grid grid-cols-1 md:grid-cols-3 gap-6 mb-10">
                <div class="bg-white p-6 rounded-xl shadow-sm card-hover transition-all duration-300">
                    <h3 class="text-xl font-semibold mb-4 text-purple-700">数据类型选择</h3>
                    <ul class="space-y-2">
                        <li>使用最小的数据类型</li>
                        <li>优先使用整型而非字符串</li>
                        <li>避免使用 TEXT/BLOB 除非必要</li>
                        <li>使用 ENUM 代替字符串</li>
                    </ul>
                </div>

                <div class="bg-white p-6 rounded-xl shadow-sm card-hover transition-all duration-300">
                    <h3 class="text-xl font-semibold mb-4 text-purple-700">范式与反范式</h3>
                    <p class="mb-3">适当的反范式化可以提升查询性能：</p>
                    <ul class="space-y-2">
                        <li>缓存派生数据</li>
                        <li>预计算聚合值</li>
                        <li>适当冗余减少 JOIN</li>
                    </ul>
                </div>

                <div class="bg-white p-6 rounded-xl shadow-sm card-hover transition-all duration-300">
                    <h3 class="text-xl font-semibold mb-4 text-purple-700">表设计技巧</h3>
                    <ul class="space-y-2">
                        <li>避免过多列</li>
                        <li>合理使用分区表</li>
                        <li>考虑垂直拆分</li>
                        <li>合理设计主键</li>
                    </ul>
                </div>
            </div>
        </section>

        <!-- Configuration Tuning -->
        <section id="configuration-tuning" class="mb-16">
            <div class="flex items-center mb-8">
                <div class="bg-orange-100 p-3 rounded-full mr-4">
                    <i class="fas fa-cog text-orange-600 text-xl"></i>
                </div>
                <h2 class="text-3xl font-bold text-gray-800">配置调优</h2>
            </div>

            <div class="bg-white rounded-xl shadow-sm overflow-hidden mb-10">
                <div class="grid grid-cols-1 md:grid-cols-3 divide-y md:divide-y-0 md:divide-x divide-gray-200">
                    <div class="p-6">
                        <h3 class="text-xl font-semibold mb-4 text-orange-700">内存配置</h3>
                        <ul class="space-y-3">
                            <li>
                                <span class="font-medium">innodb_buffer_pool_size</span>
                                <p class="text-sm text-gray-600">通常设为可用内存的 70-80%</p>
                            </li>
                            <li>
                                <span class="font-medium">key_buffer_size</span>
                                <p class="text-sm text-gray-600">MyISAM 关键缓存</p>
                            </li>
                            <li>
                                <span class="font-medium">query_cache_size</span>
                                <p class="text-sm text-gray-600">查询缓存，高并发下可能关闭</p>
                            </li>
                        </ul>
                    </div>
                    <div class="p-6">
                        <h3 class="text-xl font-semibold mb-4 text-orange-700">I/O 配置</h3>
                        <ul class="space-y-3">
                            <li>
                                <span class="font-medium">innodb_io_capacity</span>
                                <p class="text-sm text-gray-600">根据存储设备调整</p>
                            </li>
                            <li>
                                <span class="font-medium">innodb_flush_method</span>
                                <p class="text-sm text-gray-600">Linux 建议 O_DIRECT</p>
                            </li>
                            <li>
                                <span class="font-medium">sync_binlog</span>
                                <p class="text-sm text-gray-600">安全与性能的权衡</p>
                            </li>
                        </ul>
                    </div>
                    <div class="p-6">
                        <h3 class="text-xl font-semibold mb-4 text-orange-700">连接与线程</h3>
                        <ul class="space-y-3">
                            <li>
                                <span class="font-medium">max_connections</span>
                                <p class="text-sm text-gray-600">合理设置连接数</p>
                            </li>
                            <li>
                                <span class="font-medium">thread_cache_size</span>
                                <p class="text-sm text-gray-600">线程缓存大小</p>
                            </li>
                            <li>
                                <span class="font-medium">innodb_thread_concurrency</span>
                                <p class="text-sm text-gray-600">InnoDB 并发线程数</p>
                            </li>
                        </ul>
                    </div>
                </div>
            </div>
        </section>

        <!-- Visualization -->
        <section id="optimization-methods" class="mb-16">
            <div class="flex items-center mb-8">
                <div class="bg-green-100 p-3 rounded-full mr-4">
                    <i class="fas fa-chart-line text-green-600 text-xl"></i>
                </div>
                <h2 class="text-3xl font-bold text-gray-800">优化方法论</h2>
            </div>

            <div class="bg-white p-6 rounded-xl shadow-sm mb-8">
                <div class="mermaid">
                    graph TD
                        A[识别性能瓶颈] --> B[查询优化]
                        A --> C[Schema优化]
                        A --> D[配置调优]
                        B --> B1[EXPLAIN分析]
                        B --> B2[索引优化]
                        B --> B3[查询重构]
                        C --> C1[数据类型]
                        C --> C2[范式/反范式]
                        C --> C3[表设计]
                        D --> D1[内存配置]
                        D --> D2[I/O配置]
                        D --> D3[连接配置]
                </div>
            </div>
        </section>

        <!-- Performance Tuning -->
        <section id="performance-tuning" class="mb-16">
            <div class="flex items-center mb-8">
                <div class="bg-red-100 p-3 rounded-full mr-4">
                    <i class="fas fa-tachometer-alt text-red-600 text-xl"></i>
                </div>
                <h2 class="text-3xl font-bold text-gray-800">性能调优流程</h2>
            </div>

            <div class="bg-white p-6 rounded-xl shadow-sm">
                <div class="space-y-6">
                    <div class="flex items-start">
                        <div class="bg-red-500 text-white rounded-full w-8 h-8 flex items-center justify-center flex-shrink-0 mr-4">1</div>
                        <div>
                            <h3 class="text-xl font-semibold mb-2 text-gray-800">监控与基准测试</h3>
                            <p class="text-gray-600">使用性能监控工具建立性能基准，识别瓶颈点。</p>
                        </div>
                    </div>
                    <div class="flex items-start">
                        <div class="bg-red-500 text-white rounded-full w-8 h-8 flex items-center justify-center flex-shrink-0 mr-4">2</div>
                        <div>
                            <h3 class="text-xl font-semibold mb-2 text-gray-800">优化查询</h3>
                            <p class="text-gray-600">从最耗时的查询开始，使用 EXPLAIN 分析并优化。</p>
                        </div>
                    </div>
                    <div class="flex items-start">
                        <div class="bg-red-500 text-white rounded-full w-8 h-8 flex items-center justify-center flex-shrink-0 mr-4">3</div>
                        <div>
                            <h3 class="text-xl font-semibold mb-2 text-gray-800">调整 Schema</h3>
                            <p class="text-gray-600">根据查询模式优化表结构和索引。</p>
                        </div>
                    </div>
                    <div class="flex items-start">
                        <div class="bg-red-500 text-white rounded-full w-8 h-8 flex items-center justify-center flex-shrink-0 mr-4">4</div>
                        <div>
                            <h3 class="text-xl font-semibold mb-2 text-gray-800">配置调优</h3>
                            <p class="text-gray-600">根据硬件和工作负载调整 MySQL 配置参数。</p>
                        </div>
                    </div>
                    <div class="flex items-start">
                        <div class="bg-red-500 text-white rounded-full w-8 h-8 flex items-center justify-center flex-shrink-0 mr-4">5</div>
                        <div>
                            <h3 class="text-xl font-semibold mb-2 text-gray-800">持续监控</h3>
                            <p class="text-gray-600">建立持续监控机制，定期评估性能。</p>
                        </div>
                    </div>
                </div>
            </div>
        </section>

        <!-- Best Practices -->
        <section class="mb-16">
            <div class="flex items-center mb-8">
                <div class="bg-indigo-100 p-3 rounded-full mr-4">
                    <i class="fas fa-lightbulb text-indigo-600 text-xl"></i>
                </div>
                <h2 class="text-3xl font-bold text-gray-800">最佳实践</h2>
            </div>

            <div class="grid grid-cols-1 md:grid-cols-2 gap-6">
                <div class="bg-white p-6 rounded-xl shadow-sm card-hover transition-all duration-300">
                    <h3 class="text-xl font-semibold mb-4 text-indigo-700">开发阶段</h3>
                    <ul class="space-y-3">
                        <li class="flex items-start">
                            <i class="fas fa-check text-indigo-500 mt-1 mr-2"></i>
                            <span>建立合理的索引策略</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check text-indigo-500 mt-1 mr-2"></i>
                            <span>避免 N+1 查询问题</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check text-indigo-500 mt-1 mr-2"></i>
                            <span>使用连接池管理数据库连接</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check text-indigo-500 mt-1 mr-2"></i>
                            <span>考虑读写分离架构</span>
                        </li>
                    </ul>
                </div>

                <div class="bg-white p-6 rounded-xl shadow-sm card-hover transition-all duration-300">
                    <h3 class="text-xl font-semibold mb-4 text-indigo-700">运维阶段</h3>
                    <ul class="space-y-3">
                        <li class="flex items-start">
                            <i class="fas fa-check text-indigo-500 mt-1 mr-2"></i>
                            <span>定期分析和优化表</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check text-indigo-500 mt-1 mr-2"></i>
                            <span>监控慢查询日志</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check text-indigo-500 mt-1 mr-2"></i>
                            <span>定期备份和测试恢复</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check text-indigo-500 mt-1 mr-2"></i>
                            <span>考虑分库分表策略</span>
                        </li>
                    </ul>
                </div>
            </div>
        </section>
    </main>

    <!-- Footer -->
    <footer class="bg-gray-900 text-white py-10">
        <div class="container mx-auto max-w-5xl px-4">
            <div class="flex flex-col md:flex-row justify-between items-center">
                <div class="mb-4 md:mb-0">
                    <h3 class="text-xl font-semibold mb-2">技术小馆</h3>
                    <p class="text-gray-400">专业的数据库优化知识分享</p>
                </div>
                <div>
                    <a href="http://www.yuque.com/jtostring" class="text-blue-300 hover:text-white transition-colors">
                        <i class="fas fa-external-link-alt mr-2"></i>访问语雀主页
                    </a>
                </div>
            </div>
            <div class="border-t border-gray-800 mt-8 pt-8 text-center text-gray-500 text-sm">
                <p>© 2023 技术小馆. 保留所有权利.</p>
            </div>
        </div>
    </footer>

    <script>
        mermaid.initialize({
            startOnLoad: true,
            theme: 'default',
            flowchart: {
                useMaxWidth: false,
                htmlLabels: true,
                curve: 'basis'
            }
        });
    </script>
</body>
</html>